﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QuanLyGiaoVuDAL.ADOWrapper
{
    public class StoreProcedureExecutor
    {
        public static String ErrorMessage;
        public static Boolean IsError;
        private static Exception InternalException;

        public static SqlConnection GetConnection()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["QUANLYGIAOVUConnectionString"].ConnectionString;
            return connection;
        }

        public static DataSet ExecuteStoreProcedure(string StoredProcedureName)
        {
            IsError = false;
            ErrorMessage = String.Empty;
            InternalException = null;
            SqlConnection Conn = StoreProcedureExecutor.GetConnection();
            SqlCommand Command = new SqlCommand(StoredProcedureName, Conn);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(StoredProcedureName, Conn);
            Command.CommandType = CommandType.StoredProcedure;
            da.SelectCommand = Command;
            try
            {
                Conn.Open();
                da.Fill(ds);
            }
            catch(Exception exception){
                    IsError = true;
                    ErrorMessage = exception.Message;
                    InternalException = exception;
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
            if (IsError)
                throw InternalException;
            return ds;
        }

        public static DataSet ExecuteStoreProcedure(string StoredProcedure, params SqlParameter[] Parameters)
        {
            IsError = false;
            ErrorMessage = String.Empty;
            InternalException = null;
            SqlConnection Conn = StoreProcedureExecutor.GetConnection();
            SqlCommand Command = new SqlCommand(StoredProcedure, Conn);
            if (Parameters != null)
            {
                Command.Parameters.Clear();
                Command.Parameters.AddRange(Parameters);
            }
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(StoredProcedure, Conn);
            Command.CommandType = CommandType.StoredProcedure;
            da.SelectCommand = Command;
            try
            {
                Conn.Open();
                da.Fill(ds);
            }
            catch(Exception exception)
            {
                    IsError = true;
                    ErrorMessage = exception.Message;
                    InternalException = exception;
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
            if (IsError)
                throw InternalException;
            return ds;
        }

        public static bool ExecuteNonQueryStoredProcedure(string storeProcedure, params SqlParameter[] Params)
        {
            bool result = true;
            IsError = false;
            ErrorMessage = String.Empty;
            InternalException = null;
            SqlConnection Conn = StoreProcedureExecutor.GetConnection();
            SqlCommand Command = new SqlCommand(storeProcedure, Conn);
            if (Params != null)
            {
                Command.Parameters.Clear();
                Command.Parameters.AddRange(Params);
            }
            Command.CommandType = CommandType.StoredProcedure;
            try
            {
                Conn.Open();
                Command.ExecuteNonQuery();
            }
            catch(Exception exception){
                IsError = true;
                ErrorMessage = exception.Message;
                InternalException = exception;
                result = false;
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
            if (IsError)
                throw InternalException;
            return result;
        }
    }
}
